SELECT DISTINCT
(SQL.PHYSICAL_WRITE_BYTES*100)/ CASE WHEN SQL.PHYSICAL_READ_BYTES > 0 THEN SQL.PHYSICAL_READ_BYTES ELSE 1 END "% WRITE" ,
SESS.SID, SESS.SERIAL#, SESS.STATE, SESS.STATUS, SESS.PROGRAM, TO_CHAR(SESS.SQL_EXEC_START,'YYYYMMDD HH24MI') EXEC_START, SESS.BLOCKING_SESSION "BLOCKED BY", SESS.WAIT_TIME, SESS.SECONDS_IN_WAIT "WAIT SEC",
WAIT.WAIT_CLASS,
TO_CHAR(SQL.SQL_FULLTEXT), TO_CHAR(SQL.PHYSICAL_READ_BYTES,'000,000,000,000'), TO_CHAR(SQL.PHYSICAL_WRITE_BYTES,'000,000,000,000')
FROM V$SESSION SESS
INNER JOIN V$SQL SQL
ON SQL.SQL_ID = SESS.SQL_ID
LEFT JOIN V$SESSION_WAIT WAIT
ON WAIT.SID = SESS.SID
WHERE SESS.TYPE = 'USER'
AND SESS.USERNAME = 'DATPROF'
select nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/ kbytes_alloc)*100 pct_used,
nvl(largest,0) largest
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
tablespace_name
from sys.dba_data_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by PCT_USED DESC
ALTER SYSTEM KILL SESSION 'sid,201'
"grant unlimited tablespace to DATPROF;"
GRANT ALTER ANY TABLE TO DATPROF;
GRANT ALTER ANY TRIGGER TO DATPROF;
GRANT CREATE ANY INDEX TO DATPROF;
GRANT CREATE ANY PROCEDURE TO DATPROF;
GRANT CREATE ANY SEQUENCE TO DATPROF;
GRANT CREATE ANY SYNONYM TO DATPROF;
GRANT CREATE ANY TABLE TO DATPROF;
GRANT CREATE ANY VIEW TO DATPROF;
GRANT CREATE DATABASE LINK TO DATPROF;
GRANT CREATE SESSION TO DATPROF;
GRANT DROP ANY INDEX TO DATPROF;
GRANT DROP ANY PROCEDURE TO DATPROF;
GRANT DROP ANY SEQUENCE TO DATPROF;
GRANT DROP ANY TABLE TO DATPROF;
GRANT DROP ANY VIEW TO DATPROF;
GRANT INSERT ANY TABLE TO DATPROF;
GRANT SELECT ANY TABLE TO DATPROF;
GRANT UPDATE ANY TABLE TO DATPROF;
GRANT SELECT_CATALOG_ROLE TO DATPROF;
GRANT CREATE MATERIALIZED VIEW TO DATPROF;
select trigger_name from dba_triggers where table_name in (
'PROFILE',
'BPG_GEWENNINGSBIJDRAGEN',
'RPT_VEKTIS_CA319_ZZP',
'ZPG_MPT_FUNCTIES'
) AND STATUS = 'ENABLED';
select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention
from v$undostat order by end_time desc;
select * from V$UNDOSTAT;
select ceil(sum(RSSIZE)/1024/1024) "UNDO Current Used Size (MB)" from v$rollstat a;
select (sum(c.bytes)/1024/1024) "UNDO Tblspce Total Size (MB)" from dba_tablespaces b , dba_data_files c
where b.tablespace_name = c.tablespace_name and b.contents = 'UNDO' ;
SELECT OWNER, SEGMENT_NAME, TO_CHAR(BYTES/1073741824,'999,990.0') GIG FROM DBA_SEGMENTS WHERE SEGMENT_TYPE = 'TABLE'
AND SEGMENT_NAME IN ('PSN_ADRESBUITENLAND','PSN_ADRESNEDERLAND','PSN_PERSOON','PSN_PERSOON_JN')
ORDER BY BYTES/1048576 DESC
EXPLAIN PLAN FOR
EN DAARNA:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
select table_name, constraint_name, status, owner
from all_constraints
where r_owner = 'OZG_OWNER'
and constraint_type = 'R'
and r_constraint_name in
(
select constraint_name from all_constraints
where constraint_type in ('P', 'U')
and table_name = 'ALG_BAD_VARIABELEN'
and owner = 'OZG_OWNER'
)
order by table_name, constraint_name
Grootste Tabellen
voor het Oracle:
SELECT
OWNER, SEGMENT_NAME, TO_CHAR(BYTES/1073741824, '999,990.0') GIGABYTES
FROM
DBA_SEGMENTS
WHERE
SEGMENT_TYPE = 'TABLE'
AND
(
-- vervang dit door je eigen lijst met tabellen
(OWNER='PSN' AND SEGMENT_NAME='PSN_PERSOON')
OR (OWNER='PSN' AND SEGMENT_NAME='PSN_PERSOON_JN')
OR (OWNER='PSN' AND SEGMENT_NAME='PSN_ADRESBUITENLAND')
OR (OWNER='PSN' AND SEGMENT_NAME='PSN_ADRESNEDERLAND')
)
ORDER BY 3 DESC
Voor database:
SELECT
s.Name AS SchemaName,
t.NAME AS TableName,
CAST(ROUND(((SUM(a.total_pages) * 8) / (1024.00*1024.00)), 2) AS NUMERIC(36, 1)) AS TotalSpaceGB,
CAST(ROUND(((SUM(a.used_pages) * 8) / (1024.00*1024.00)), 2) AS NUMERIC(36, 1)) AS UsedSpaceGB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME in ('tblDebiteur', 'tblFactuur')
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
Hoe groot zijn Oracle LOBs?
SELECT
L.OWNER,
L.TABLE_NAME,
TO_CHAR(BYTES/1073741824,'999,990.0') "SIZE (GB)"
FROM
DBA_SEGMENTS S, DBA_LOBS L
WHERE
S.SEGMENT_NAME = L.SEGMENT_NAME
AND L.TABLE_NAME IN
(
-- vervang dit door je eigen lijst met tabellen
'GUW_BERICHT',
'GUW_BERICHTENSETINKOMEND',
'GUW_BERICHTREGEL')
ORDER BY S.SEGMENT_NAME, S.PARTITION_NAME;
INSERT INTO VT_BTL_VNM
SELECT
A.VOORNAAM VOORNAAM,
B.VOORNAAM VOORNAAM_PNEW,
A.VERZEKERDE_ID VERZEKERDE_ID,
A.SOFINUMMER SOFINUMMER
FROM (
SELECT
ROW_NUMBER () OVER (ORDER BY VERZEKERDE_ID) AS RW, VOORNAAM, VERZEKERDE_ID, SOFINUMMER
FROM (
SELECT
VT_BTL_NAMEN_TMP.VOORNAAM,
VT_BTL_NAMEN_TMP.VERZEKERDE_ID,
VT_BTL_NAMEN_TMP.SOFINUMMER
FROM VT_BTL_NAMEN_TMP
LEFT JOIN VT_BTL_VNM
ON VT_BTL_VNM.VERZEKERDE_ID = VT_BTL_NAMEN_TMP.VERZEKERDE_ID
AND VT_BTL_VNM.SOFINUMMER = VT_BTL_NAMEN_TMP.SOFINUMMER
WHERE VT_BTL_VNM.VERZEKERDE_ID IS NULL
OR VT_BTL_VNM.SOFINUMMER IS NULL
)
) A,
(
SELECT
ROW_NUMBER () OVER (ORDER BY DBMS_RANDOM.VALUE) AS RW, VOORNAAM
FROM (
SELECT
VT_BTL_NAMEN_TMP.VOORNAAM
FROM VT_BTL_NAMEN_TMP
LEFT JOIN VT_BTL_VNM
ON VT_BTL_VNM.VERZEKERDE_ID = VT_BTL_NAMEN_TMP.VERZEKERDE_ID
AND VT_BTL_VNM.SOFINUMMER = VT_BTL_NAMEN_TMP.SOFINUMMER
WHERE VT_BTL_VNM.VERZEKERDE_ID IS NULL
OR VT_BTL_VNM.SOFINUMMER IS NULL
)
) B
WHERE A.RW = B.RW
';
https://jira.datprof.com/servicedesk/customer/portal/6/user/login?destination=portal%2F6
create table RBH_RELATIES_BACKUP as select * from RBH_RELATIES';
CREATE TABLE DummyX
(
DummyX_supplier_id number(10) NOT NULL,
supplier_name varchar2(50) NOT NULL,
address varchar2(50),
city varchar2(50),
state varchar2(25),
zip_code varchar2(10)
);